Excel VBA Tips: Large concatenation OR concatenating multiple ranges

Excel VBA Tips: Large concatenation OR concatenating multiple ranges

One of the useful worksheet functions in excel is being able to concatenate cells into a single cell. However, if you try to concatenate a large number of cells or a multiple ranges, it can become very problematic.

However, after searching today, I came across this code:

The MultiCat() function allows you to use a more compact syntax:

  = MultiCat(A1:C1," ")

MultiCat concatenates the text from the cells, rather than their underlying values. If you have the number 1234 in a cell formatted as “00000”, MultiCat will return “01234” while CONCATENATE will return 1234.

Put this in a regular code module.

  '*****************************************
  'Purpose: Concatenate all cells in a range
  'Inputs:  rRng - range to be concatenated
  '         sDelimiter - optional delimiter
  '            to insert between cell Texts
  'Returns: concatenated string
  '*****************************************
  Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
         MultiCat = MultiCat & sDelim & rCell.Text
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
  End Function

Fantastic! Works perfectly.

Come check out the directory for the rest of my excel tips!

Submeg

IT